package TeachLesson;

import javax.print.DocFlavor;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

//答疑讨论业务逻辑类
public class SolveProDao {
    public Connection getCon(){
        Connection con=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url="jdbc:mysql://localhost:3306/jwglxt?serverTimezone=UTC&characterEncoding=utf-8";
            con= DriverManager.getConnection(url,"root","root");
        }catch (Exception e){
            e.printStackTrace();
        }
        return con;
    }

    //新增帖子
    public int addSolvePro(String workerNo,String courseNo,String title,String realText){
          Connection con=this.getCon();
          try {
              String sql="insert into tb_solvepro (workerNo,courseNo,title,realText) values (?,?,?,?)";
              PreparedStatement ps=con.prepareStatement(sql);
              ps.setString(1,workerNo);
              ps.setString(2,courseNo);
              ps.setString(3,title);
              ps.setString(4,realText);
              //执行
              int row=ps.executeUpdate();
              ps.close();
              con.close();
              return row;
          }catch (Exception e){
              e.printStackTrace();
          }
          return 0;
    }

    //查询所有帖子
    public ArrayList<SolveProEnity> selectAllPro(){
        Connection con=this.getCon();
        try {
            String sql="select * from tb_solvepro ";
            PreparedStatement ps=con.prepareStatement(sql);
            ResultSet rs=ps.executeQuery();
            ArrayList<SolveProEnity> list=new ArrayList<>();
            while (rs.next()){
                SolveProEnity solveProEnity=new SolveProEnity();
                solveProEnity.setTitleId(rs.getInt("titleID"));
                solveProEnity.setWorkerNo(rs.getString("workerNo"));
                solveProEnity.setCourseNo(rs.getString("courseNo"));
                solveProEnity.setTitle(rs.getString("title"));
                solveProEnity.setRealText(rs.getString("realText"));
                list.add(solveProEnity);
            }
            rs.close();
            ps.close();
            con.close();
            return list;
        }catch (Exception e){
           e.printStackTrace();
           return null;
        }
    }

    //修改帖子内容
    public int updateSolvePro(int titleID,String title,String realText){
        Connection con=this.getCon();
        try {
            //sql语句
            String sql="update tb_solvepro set title=?,realtext=? where titleID=?";
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setString(1,title);
            ps.setString(2,realText);
            ps.setInt(3,titleID);
            //执行sql语句
            int row=ps.executeUpdate();
            ps.close();
            con.close();
            return row;
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }
    }

    //删除答疑讨论
    public int deleteSolvePro(int titleID){
        Connection con=this.getCon();
        try {
            String sql="delete from tb_solvepro where titleID =?";
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setInt(1,titleID);
            int row=ps.executeUpdate();
            ps.close();
            con.close();
            return row;
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }
    }
}
